
package dbPicasso::Database::Pg::db;

use dbPicasso::Database::Helper;
use base qw(dbPicasso::Database::Helper::db);

sub _gen_DSN
{
	my $class = shift;
	my %cxinfo = @_;

	my $host = $cxinfo{Host} || "localhost";
	return undef unless ($cxinfo{Database});

	my $reqDSN = join (":", "dbi", $cxinfo{Driver}, 
				join (";", "host=$host",
					   "db=$cxinfo{Database}"
				     )
			  );

	return $reqDSN;
}

sub get_object_list 
{
	my $self = shift;
	my $reqtype = shift;
	
	my $sql =<<'EOSQL';
	
	select specific_schema as schemaname, routine_name as name, 'sproc' as type
	from information_schema.routines
	--where ns.nspname not in ('information_schema', 'pg_catalog')
	union all
	select    table_schema as schema
		, table_name as name
		, case table_type when 'VIEW' then 'view'
		                  when 'BASE TABLE' then 'table'
		  end as type
	from information_schema.tables
	where table_type in ('BASE TABLE', 'VIEW') -- shouldn't need this, but...
	--where table_schema not in ('information_schema', 'pg_catalog')

EOSQL

    my %objects;
    my $sth = $self->prepare($sql);
    $sth->execute;
    
    while (my $row = $sth->fetchrow_hashref)
    {
	my $schema = $row->{schemaname};
	my $type = $row->{type};
	
	$objects{$schema} = {} unless defined $objects{ $schema };
	$objects{$schema}{$type} = [] unless defined $objects{ $schema }{$type};
	
	push @{ $objects{$schema}{$type} }, $row;
    }
    
    return ($objects{$reqtype}) if ($reqtype);
    return \%objects;
}

sub get_object_depends
{
	my $self = shift;
	my $objname = shift;
	
	return $self->execQuery('select 1 as "Not Implemented"');
}

sub get_object_indexes
{
	my $self = shift;
	my $objname = shift;
	
	return $self->execQuery('select 1 as "Not Implemented"');
}

sub get_object_info 
{
	my $self = shift;
	my $objname = shift;
	
	return $self->execQuery('select 1 as "Not Implemented"');
}

sub get_object_ddl
{
    my $self = shift;
    my $name = shift;
    my $type = shift;
    
    print "Pg: Getting DDL for $type $name...\n";

    return $self->_get_sproc_ddl($name) if $type eq 'sproc';
    return $self->_get_table_ddl($name) if $type eq 'table';
    return $self->_get_view_ddl($name) if $type eq 'view';
    return undef;
}

sub _get_table_ddl
{
    my $self = shift;
    my $name = shift;

    # We need to add support for schemas here. This will just grab the columns
    # for any table with the given name. That'll probably work by coincidence
    # a startling number of times, but could lead to some oddball behavior
    # when two schemas have a table with the same name. 

    my $sql=<<SQL;
    
    select table_schema, table_name, column_name, data_type, column_default,
           character_maximum_length, numeric_precision_radix, numeric_scale
    from information_schema.columns
    where table_name = ? 
    order by ordinal_position
    
SQL
    
    my $sth = $self->prepare($sql);
    $sth->execute($name);
    
    my $ddl = sprintf "create table %s\n(\n", $name;
    my @ddl;
    while (my $column = $sth->fetchrow_hashref)
    {
	my $colname = $column->{column_name};
	my $dtype = $column->{data_type};
	
	if ($dtype eq 'character varying')
	{
		$dtype = 'varchar';
		$dtype .= sprintf "(%s)", $column->{character_maximum_length}
			if ($column->{character_maximum_length});
	}
	
	my $constraint;
	
	if ($column->{column_default})
	{
		$constraint = sprintf 'default %s', $column->{column_default};
	}
	
	push @ddl, sprintf "\t%s\t%s %s", $colname, $dtype, $constraint;
    }
    
    $ddl .= join(",\n", @ddl) . "\n);\n";
    
    return $ddl;
}

sub _get_view_ddl
{
    my $self = shift;
    my $name = shift;
    
    my $sql = <<EOSQL;
    
    select view_definition
    from information_schema.views 
    where table_name = ?
    
EOSQL

    my $sth = $self->prepare($sql);
    $sth->execute($name);
    
    my $row = $sth->fetchrow_arrayref;
    
    return $$row[0];
    
}

sub _get_sproc_ddl
{
    my $self = shift;
    my $name = shift;
    my $sql = <<EOSQL;
    
    select prosrc from pg_catalog.pg_proc 
    where proname = ?
    
EOSQL

    my $sth = $self->prepare($sql);
    $sth->execute($name);
    
    my $row = $sth->fetchrow_arrayref;
    
    return $$row[0];


}

package dbPicasso::Database::Pg::st;

use base qw(dbPicasso::Database::Helper::st);


1;
